package com.thh.datamonitor.executor;

import cn.hutool.core.util.StrUtil;
import com.thh.common.enums.DatabaseDriverEnums;
import com.thh.common.exception.ServiceException;
import com.thh.common.exception.sql.SqlException;
import com.thh.common.utils.AESUtils;
import com.thh.common.utils.StringUtils;
import com.thh.common.utils.bean.BeanUtils;
import com.thh.common.utils.sql.SqlUtil;
import com.thh.datamonitor.config.DataMonitorConfig;
import com.thh.datamonitor.dto.*;
import com.thh.datamonitor.executor.dao.CommonDao;
import com.thh.datamonitor.param.ExcuteAndShowExecSqlDataParam;
import com.thh.datamonitor.entity.TmdDatabase;
import com.thh.datamonitor.param.TmdSQLShowParam;
import com.thh.datamonitor.param.TmdTableInfoQueryParam;
import com.thh.datamonitor.param.TmdTableQueryParam;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;

/*
 * @Author tanhuihuang
 * @Description  统一逻辑层关链接，一个逻辑可能存在多次调用数据库操作
 * @Date 16:57 2022/6/27
 **/
public abstract class AbstractDbExecutor extends CommonDao {

    private final static Logger logger = LoggerFactory.getLogger(AbstractDbExecutor.class);

    @Autowired
    private DataMonitorConfig dataMonitorConfig;

    public abstract String genUrl(TmdDatabase database);

    public abstract Connection getConnection(TmdDatabase database);

    // 获取列表list
    public abstract List<TmdTableDto> getTableList(TmdDatabase tmdDatabaseDto, TmdTableQueryParam tmdTableQueryParam);

    // 获取ddl
    public abstract String ddl(TmdDatabase tmdDatabaseDto, TmdTableQueryParam tmdTableQueryParam);

    // 获取主键
    public abstract String getPrimaryKey(TmdDatabase database, String tableName);


    // 执行查询语句
    public abstract TableInfoListDto executeSelectSql(TmdDatabase database, String sql) throws Exception;

    // 解密密码
    protected String getDataPassword(TmdDatabase database) {
        if (database == null) {
            throw new ServiceException("数据库配置不能为空！");
        }
        String databasePassword = database.getDatabasePassword();
        try {
            databasePassword = AESUtils.decrypt(databasePassword, dataMonitorConfig.getDatabaseAes());
        } catch (Exception e) {
            logger.error("AbstractDbExecutor.getDataPassword error", e);
            throw new ServiceException("密码解密失败,error " + e.getMessage());
        }
        return databasePassword;
    }

    public void testConnection(TmdDatabaseDto database) {
        String password = database.getDatabasePasswordShow();
        if (StringUtils.isBlank(password)) {
            String databasePassword = database.getDatabasePassword();
            if (StringUtils.isNotBlank(databasePassword)) {
                try {
                    password = AESUtils.decrypt(databasePassword, dataMonitorConfig.getDatabaseAes());
                } catch (Exception e) {
                    logger.error("AbstractDbExecutor.decryptDatabasePassword error", e);
                    throw new ServiceException("密码解密失败,error " + e.getMessage());
                }
            }
        }
        if (StringUtils.isBlank(password)) {
            throw new ServiceException("密码不能为空！");
        }

        DatabaseDriverEnums driver = DatabaseDriverEnums.getByName(database.getDatabaseType());
        Connection connection = null;
        try {
            Class.forName(driver.getDriverClassName());
            String url = this.genUrl(database);
            connection = DriverManager.getConnection(url, database.getDatabaseUser(), password);
        } catch (Exception e) {
            logger.error("测试连接失败！errorMsg:" + e.getMessage());
            throw new ServiceException("测试连接失败！errorMsg:" + e.getMessage());
        } finally {
            close(connection);
        }
    }

    public void executeInsertSqlNoCommit(TmdDatabase database, String sql) throws Exception {
        Connection connection = getConnection(database);
        try {
            executeInsertSqlNoCommit(connection, sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(connection);
        }
    }




    public TableInfoListDto tableInfoListDto(TmdDatabase database, TmdTableInfoQueryParam tmdTableQueryParam) throws Exception {
        String sql = "select * from " + tmdTableQueryParam.getTableName();
        if (!StrUtil.hasEmpty(tmdTableQueryParam.getCondition())) {
            sql += " where " + tmdTableQueryParam.getCondition();
        }
        TableInfoListDto tableInfoListDto = executeSelectSql(database, sql);
        String primaryKey = getPrimaryKey(database, tmdTableQueryParam.getTableName());
        tableInfoListDto.setPrimaryKey(primaryKey);
        return tableInfoListDto;
    }

    public ExcuteAndShowShowDto excuteAndShowList(TmdDatabase database, TmdSQLShowParam tmdSQLShowParam) throws Exception {
        String sql = SqlUtil.removeComments(tmdSQLShowParam.getSql());
        TableInfoListDto tableInfoListDto = executeSelectSql(database, sql);
        ExcuteAndShowShowDto excuteAndShowShowDto = new ExcuteAndShowShowDto();
        BeanUtils.copyBeanProp(tableInfoListDto, excuteAndShowShowDto);
        excuteAndShowShowDto.setSql(sql);
        return excuteAndShowShowDto;
    }

    public ExcuteAndShowExecSqlDataDto execSql(TmdDatabase database, ExcuteAndShowExecSqlDataParam excuteAndShowExecSqlDataParam) throws Exception {
        String runSql = excuteAndShowExecSqlDataParam.getRunSql();
        if (StringUtils.isBlank(runSql)) {
            throw new SqlException("执行的sql不能为空！");
        }

        List<String> sqlList = Arrays.asList(runSql.split(";"));
        // 校验db权限
        ExcuteAndShowExecSqlDataDto excuteAndShowExecSqlDataDto = new ExcuteAndShowExecSqlDataDto();
        Connection connection = null;
        StringBuilder sbRunLog = new StringBuilder();
        try {
            connection = getConnection(database);
            // 查询列表展示
            List<TableInfoListDto> resultList = new ArrayList<>();
            for (String sql : sqlList) {
                long start = System.currentTimeMillis();
                sbRunLog.append("【命令】 ").append(sql).append("\r\n");
                sql = SqlUtil.removeComments(sql);
                try {
                    if (sql.toLowerCase().contains("select") || sql.toLowerCase().contains("show")) {
                        TableInfoListDto tableInfoListDto = executeSelectSql(database, sql);
                        resultList.add(tableInfoListDto);
                        long count = tableInfoListDto.getTotal();
                        sbRunLog.append("> OK,查询出 ").append(count).append(" 行\r\n");
                    } else {
                        long count = executeInsertUpdateDeleteSql(connection, sql, dataMonitorConfig.getDataChangeMaxCount());
                        sbRunLog.append("> OK,影响 ").append(count).append(" 行\r\n");
                    }
                    sbRunLog.append("> 时间: ").append(System.currentTimeMillis() - start).append("ms\r\n");
                    excuteAndShowExecSqlDataDto.setResultList(resultList);
                    excuteAndShowExecSqlDataDto.setRunLog(sbRunLog.toString());
                } catch (SqlException e) {
                    sbRunLog.append("> ERROR: ").append(e.getMessage()).append("\r\n");
                    sbRunLog.append("> 时间: ").append(System.currentTimeMillis() - start).append("ms\r\n");
                    excuteAndShowExecSqlDataDto.setRunLog(sbRunLog.toString());
                    throw e;
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(connection);
        }
        // 记录日志操作
        return excuteAndShowExecSqlDataDto;
    }
}
